package com.munhrasoft.dinamica.business.logic;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.munhrasoft.dinamica.business.entities.Cliente;
import com.munhrasoft.dinamica.business.entities.CustoCNotas;

import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;

@Stateless
/**
 * Esta classe vai carregar a tabela de custos do cartorio de notas
 * @author munhra
 *
 */
public class CustoCNotasEJB {

    @PersistenceContext(unitName = "dinamicaPU")
    private EntityManager   em;

    private static Workbook wk;
    
    public double custoPorValor(double valor){
        Query query = em.createNamedQuery("findCusto");
        query.setParameter("valor", valor);
        // se for maior que zero tem inconsistencia na planilha de entrada
        // se nao possuir elementos tem inconsistencia tambem
        
        if (query.getResultList().size() > 0 && query.getResultList().size() <= 1){
            CustoCNotas cnotas = (CustoCNotas)query.getResultList().get(0);            
            return cnotas.getCusto();
        }else{
            //erro de incosistencia, a tabela esta errada
            return -1;
        }
    }
    
    public void reloadCustoCNotas(InputStream is){
        
        WorkbookSettings ws = new WorkbookSettings();
        ws.setEncoding("Cp1252");
        try {
            wk = Workbook.getWorkbook(is, ws);
        } catch (BiffException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        removeAll();

        // carrega os novos dados

        Sheet custoSheet = wk.getSheets()[0];

        for (int row = 0; row < custoSheet.getRows(); row++) {
            CustoCNotas custoNotas = new CustoCNotas();
            custoNotas.setIntervalomin(Double.valueOf(custoSheet.getCell(0, row).getContents().replace(",",".")));
            custoNotas.setIntervalomax(Double.valueOf(custoSheet.getCell(1, row).getContents().replace(",",".")));
            custoNotas.setCusto(Double.valueOf(custoSheet.getCell(2, row).getContents().replace(",",".")));
            em.persist(custoNotas);
        }


        
    }

    public void reloadCustoCNotas(String filePath) {
        WorkbookSettings ws = new WorkbookSettings();
        ws.setEncoding("Cp1252");
        try {
            wk = Workbook.getWorkbook(new File(filePath), ws);
        } catch (BiffException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        removeAll();

        // carrega os novos dados

        Sheet custoSheet = wk.getSheets()[0];

        for (int row = 0; row < custoSheet.getRows(); row++) {
            CustoCNotas custoNotas = new CustoCNotas();
            custoNotas.setIntervalomin(Double.valueOf(custoSheet.getCell(0, row).getContents().replace(",",".")));
            custoNotas.setIntervalomax(Double.valueOf(custoSheet.getCell(1, row).getContents().replace(",",".")));
            custoNotas.setCusto(Double.valueOf(custoSheet.getCell(2, row).getContents().replace(",",".")));
            em.persist(custoNotas);
        }

    }
    
    @SuppressWarnings("unchecked")
    public List<CustoCNotas> fetchAllCustoCNotas() {
        Query query = em.createNamedQuery("findAllCNotasLines");
        return query.getResultList();
    }

    private void removeAll() {
        Query query = em.createNamedQuery("findAllCNotasLines");      
        Object[] cnotas =  query.getResultList().toArray();                
        if (cnotas.length > 0) {            
            for (int i = 0; i < cnotas.length; i++) {
                em.remove(em.merge(cnotas[i]));
            }
        }
    }

}
